package org.zhu.table2word;

import org.zhu.table2word.bean.FieldInfo;
import org.zhu.table2word.bean.MysqlConfig;
import org.zhu.table2word.bean.TableField;
import org.zhu.table2word.bean.WxUser;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class XingliClient {
    public static void main(String[] args) throws Exception {
        MysqlConfig mysqlConfig =getMysqlConfig();
        Connection conn = null;
        PreparedStatement pstmt = null;
        conn =  getConnections(mysqlConfig);

        pstmt = conn.prepareStatement("SELECT * FROM `wx_user`;");
        ResultSet tableResultSet = pstmt.executeQuery();
        List<WxUser> wxUsers = new ArrayList<>();
        while (tableResultSet.next()){
            WxUser wxUser = new WxUser(
                    Integer.decode(tableResultSet.getString("id")),
                    tableResultSet.getString("nickname"),
                    1,
                    tableResultSet.getString("gender"),
                    tableResultSet.getString("city"),
                    tableResultSet.getString("province"),
                    tableResultSet.getString("country"),
                    tableResultSet.getString("avatar_url"),
                    tableResultSet.getString("union_id"),
                    tableResultSet.getString("open_id"),
                    tableResultSet.getString("add_time"),
                    tableResultSet.getString("max_score")

            );
            wxUsers.add(wxUser);
        }
        int i = 1;
        for (WxUser wxUser:wxUsers){
            wxUser.setId(i++);
        }
        conn.close();


        mysqlConfig =getMysqlConfigMY();
        conn =  getConnections(mysqlConfig);


        for (WxUser wxUser:wxUsers){
            String insertStr = String.format("INSERT INTO tens_user_ext (user_id,is_vip,max_score) VALUES (%d,%d,%s);",wxUser.getId(),0,wxUser.getMax_score());
            pstmt = conn.prepareStatement(insertStr);
            pstmt.executeUpdate();
        }

        for (WxUser wxUser:wxUsers){
            String insertStr = String.format("INSERT INTO wx_user" +
                    " (id,add_time,app_id,avatar_url,city,country,game_id,gender,nickname,open_id,province,union_id)" +
                            " VALUES (%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");",
                    wxUser.getId(),wxUser.getAdd_time(),"",wxUser.getAvatar_url(),wxUser.getCity(),wxUser.getCountry(),wxUser.getGame_id(),wxUser.getGender(),
                    wxUser.getNickname(),wxUser.getOpen_id(),wxUser.getProvince(),"");

            System.out.println(insertStr);
            pstmt = conn.prepareStatement(insertStr);
            pstmt.executeUpdate();
        }




    }

    private static MysqlConfig getMysqlConfig(){

        return new MysqlConfig("jdbc:mysql://47.98.128.196:3306/tens?useUnicode=true&characterEncoding=UTF-8",
                "zhu","olive888","com.mysql.jdbc.Driver");
    }

    private static MysqlConfig getMysqlConfigMY(){

        return new MysqlConfig("jdbc:mysql://localhost:3306/norain?useUnicode=true&characterEncoding=UTF-8",
                "root","","com.mysql.jdbc.Driver");
    }

    //获取连接
    private static Connection getConnections(MysqlConfig mysqlConfig) throws Exception {
        Connection conn = null;
        try {
            Properties props = new Properties();
            props.put("remarksReporting", "true");
            props.put("user", mysqlConfig.getUsername());
            props.put("password", mysqlConfig.getPassword());
            Class.forName(mysqlConfig.getDriver());
            conn = DriverManager.getConnection(mysqlConfig.getUrl(), props);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return conn;
    }
}
